﻿
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

using System.Data;
using System.Data.OleDb;
using shopsoft.Common;
using shopsoft.Common.Security;

namespace shopsoft.dbutility
{
    /// <summary>
    /// 数据处理公共类
    /// </summary>
    public class MsSqlHelper
    {
        #region Private Attribile

        /// <summary>
        /// 数据库连接对象
        /// </summary>
        private OleDbConnection _DbConn;

        #endregion

        #region Structure Method

        /// <summary>
        /// 构造函数初始化当前数据库连接的dbConOrder 属性
        /// </summary>
        public MsSqlHelper()
        {
            _DbConn = new OleDbConnection(GetDbConnString());
        }

        #endregion

        #region Private Method

        /// <summary>
        /// 获得数据库连接串
        /// </summary>
        /// <returns></returns>
        private static string GetDbConnString()
        {
            return Des.Decrypt(new PubConfig().DbConString);
        }

        /// <summary>
        /// 构建 OleDbCommand 对象(用来返回一个结果集，而不是一个整数值)
        /// </summary>
        /// <param name="connection">数据库连接</param>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <param name="iFlag">标记位[1：存储过程；2：非存储过程]</param>
        /// <returns>SqlCommand</returns>
        private OleDbCommand BuildQueryCommand(OleDbConnection connection, string storedProcName, OleDbParameter[] parameters, int iFlag)
        {
            var command = new OleDbCommand(storedProcName, connection);
            
            if (iFlag == 1)
            {
                command.CommandType = CommandType.StoredProcedure;
            }
            if (parameters != null)
            {
                foreach (var parameter in parameters)
                {
                    if (parameter != null)
                    {
                        // 检查未分配值的输出参数,将其分配以DBNull.Value.
                        if ((parameter.Direction == ParameterDirection.InputOutput ||
                             parameter.Direction == ParameterDirection.Input) &&
                            (parameter.Value == null))
                        {
                            parameter.Value = DBNull.Value;
                        }
                        command.Parameters.Add(parameter);
                    }
                }
            }
            return command;
        }

        #endregion

        #region Public Method
               
        /// <summary>
        /// 执行存储过程或Sql语句，返回OleDbDataReader
        /// </summary>
        /// <param name="storedProcName">存储过程名或是Sql语句</param>
        /// <param name="parameters">参数</param>
        /// <param name="intFlag">标记(1：存储过程；2：非存储过程)</param>
        /// <param name="returnReader">返回DataReader</param>
        public void RunProcedureOrSql(string storedProcName, OleDbParameter[] parameters, int intFlag, out OleDbDataReader returnReader)
        {
            _DbConn.Open();
            OleDbCommand command = BuildQueryCommand(_DbConn, storedProcName, parameters, intFlag);
            //command.CommandType = CommandType.StoredProcedure;
            returnReader = command.ExecuteReader(CommandBehavior.CloseConnection);
        }

        /// <summary>
        /// 执行存储过程或Sql语句,返回DataSet
        /// </summary>
        /// <param name="storedProcName">存储过程名或是Sql语句</param>
        /// <param name="parameters">参数</param>
        /// <param name="tableName">DataSet结果中的表名</param>
        /// <param name="intFlag">标记(1：存储过程；2：非存储过程)</param>
        /// <param name="returnDataSet">返回DataSet</param>
        public void RunProcedureOrSql(string storedProcName, OleDbParameter[] parameters, string tableName, int intFlag, out DataSet returnDataSet)
        {
            using (var connection = new OleDbConnection(GetDbConnString()))
            {
                var ds = new DataSet();
                connection.Open();
                var sqlDa = new OleDbDataAdapter();
                sqlDa.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters, intFlag);
                sqlDa.Fill(ds, tableName);
                returnDataSet = ds;
                connection.Close();
            }
        }

        /// <summary>
        /// 执行存储过程或Sql语句,返回DataSet
        /// </summary>
        /// <param name="storedProcName">存储过程名或是Sql语句</param>
        /// <param name="parameters">参数</param>
        /// <param name="tableName">DataSet结果中的表名</param>
        /// <param name="times">CommandTimeout的值</param>
        /// <param name="intFlag">标记(1：存储过程；2：非存储过程)</param>
        /// <param name="returnDataSet">返回DataSet</param>
        public void RunProcedureOrSql(string storedProcName, OleDbParameter[] parameters, string tableName, int times, int intFlag, out DataSet returnDataSet)
        {
            using (var connection = new OleDbConnection(GetDbConnString()))
            {
                var ds = new DataSet();
                connection.Open();
                var sqlDa = new OleDbDataAdapter();
                sqlDa.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters, 1);
                sqlDa.SelectCommand.CommandTimeout = times;
                sqlDa.Fill(ds, tableName);
                returnDataSet = ds;
                connection.Close();
            }
        }

        /// <summary>
        /// 执行存储过程或Sql语句，返回影响的行数		
        /// </summary>
        /// <param name="storedProcName">存储过程名或是Sql语句</param>
        /// <param name="parameters">存储过程参数</param>
        /// <param name="intFlag">标记(1：存储过程；2：非存储过程)</param>
        /// <returns>影响行数</returns>
        public int RunProcedureOrSql(string storedProcName, OleDbParameter[] parameters, int intFlag)
        {
            using (var connection = new OleDbConnection(GetDbConnString()))
            {
                connection.Open();
                var command = BuildQueryCommand(connection, storedProcName, parameters, intFlag);
                int rowsCount = command.ExecuteNonQuery();
                //foreach (var paras in parameters.Where(paras => paras.ParameterName == "@EffectCount"))
                //{
                //    //存储过程内部人工统计业务上的影响行数
                //    rowsCount = Convert.ToInt32(paras.Value);
                //    break;
                //}
                connection.Close();
                return rowsCount;
            }
        }

        #endregion
    }
}
